unit clsSubsttrib;

interface

uses uDMCI, DbTables, ubiblioCI, sysutils;

type
  TCLSSubstTrib = class(TObject)
  private
    vUFSIGLA : string;


  public
    vUF_ID : INTEGER;
    vUFDESCR, vUF_IESTADUAL_SUBSTITUTOTRIB : String;

    vSOMASTNF,
    vISUB_UF_SIGLA : string;
    vISUB_ID_NCM,
    vISUB_ALIQUOTA_INTERNA,
    vISUB_MARGEM_IVA,
    vISUB_REDUCAO_BCALC,
    vISUB_PERCENT_SUBSTIT : Double;
    vMODBCST : String;


    function GravarUF(pUFSIGLA: String;PISUB_ID_NCM : double): Byte;
    function Excluir(pUFSIGLA: String): Byte;
    procedure Consultar(var p_q: TQuery);
    function RetornaMaiorCodigo: Integer;
    procedure Consultar2(var p_q: TQuery; vUFDESCR: string);
    procedure ConsultarNCM(var p_q: TQuery);
    procedure ConsultarConfig(var p_q: TQuery);
    function Gravar(pUFSIGLA: String): Byte;
  end;

implementation

{ TCodFiscal }

procedure TCLSSubstTrib.Consultar(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT EST_NOME AS UFDESCR, EST_SIGLA AS UFSIGLA, EST_SIGLA||'+#39+' - '+#39+'||EST_NOME AS DescriUF FROM ESTADO ORDER BY EST_NOME ');
end;

procedure TCLSSubstTrib.ConsultarNCM(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT M.DESCRICAO, M.MNCM_NF_CLASSFISCAL, M.MNCM_ID_NCM, M.MNCM_DESCRICAO_TIPI, M.MNCM_CODIGO,  M.MNCM_CODIGO||'#39+' - '#39+'||M.DESCRICAO AS ClassNCM, M.MNCM_EXTIPI '+
              ' FROM MERCADORIA_NCM M ORDER BY M.MNCM_CODIGO ');
end;

procedure TCLSSubstTrib.ConsultarConfig(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT I.*, U.EST_SIGLA||'+#39+' - '+#39+'||U.EST_NOME AS DescUFST, M.MNCM_CODIGO||'#39+' - '#39+'||M.DESCRICAO AS ClassNCM FROM IMPOSTOS_SUBSTITUICAO I JOIN ESTADO U ON (I.ISUB_UF_SIGLA = U.EST_SIGLA) JOIN MERCADORIA_NCM M ON (I.ISUB_ID_NCM = M.MNCM_ID_NCM) ');
end;

procedure TCLSSubstTrib.Consultar2(var p_q: TQuery; vUFDESCR: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT * FROM MERCADORIA_NCM WHERE DESCRICAO LIKE (:vUFDESCR) ORDER BY DESCRICAO');
  p_q.parambyname('vUFDESCR').asstring := vUFDESCR;
end;

function TCLSSubstTrib.Excluir(pUFSIGLA: String): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM MERCADORIA_NCM where MNCM_ID_NCM=:pUFSIGLA');
  tq.parambyname('pUFSIGLA').AsString := pUFSIGLA;
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

function TCLSSubstTrib.GravarUF(pUFSIGLA: String;PISUB_ID_NCM : double): Byte;
var
  tq, tq1: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add('SELECT * FROM IMPOSTOS_SUBSTITUICAO WHERE ISUB_UF_SIGLA = :PISUB_UF_SIGLA AND ISUB_ID_NCM = :P_ISUB_ID_NCM ');
  tq.parambyname('PISUB_UF_SIGLA').AsString := pUFSIGLA;
  tq.parambyname('P_ISUB_ID_NCM').Asfloat   := PISUB_ID_NCM;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO IMPOSTOS_SUBSTITUICAO (ISUB_UF_SIGLA,ISUB_ID_NCM,ISUB_ALIQUOTA_INTERNA,ISUB_MARGEM_IVA,ISUB_REDUCAO_BCALC,ISUB_PERCENT_SUBSTIT, SOMASTNF, MODBCST) '+
                ' VALUES (:P_ISUB_UF_SIGLA,:P_ISUB_ID_NCM,:P_ISUB_ALIQUOTA_INTERNA,:P_ISUB_MARGEM_IVA,:P_ISUB_REDUCAO_BCALC,:P_ISUB_PERCENT_SUBSTIT, :P_SOMASTNF, :P_MODBCST) ');
  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE IMPOSTOS_SUBSTITUICAO SET ISUB_ALIQUOTA_INTERNA = :P_ISUB_ALIQUOTA_INTERNA,ISUB_MARGEM_IVA = :P_ISUB_MARGEM_IVA, '+
                ' ISUB_REDUCAO_BCALC = :P_ISUB_REDUCAO_BCALC,ISUB_PERCENT_SUBSTIT = :P_ISUB_PERCENT_SUBSTIT, SOMASTNF = :P_SOMASTNF, MODBCST = :P_MODBCST '+
                ' WHERE ISUB_UF_SIGLA = :P_ISUB_UF_SIGLA AND ISUB_ID_NCM = :P_ISUB_ID_NCM ');
  end;
  tq1.parambyname('P_ISUB_UF_SIGLA').AsString        := pUFSIGLA;
  tq1.parambyname('P_ISUB_ID_NCM').Asfloat           := PISUB_ID_NCM;
  tq1.parambyname('P_ISUB_ALIQUOTA_INTERNA').Asfloat := vISUB_ALIQUOTA_INTERNA;
  tq1.parambyname('P_ISUB_MARGEM_IVA').Asfloat       := vISUB_MARGEM_IVA;
  tq1.parambyname('P_ISUB_REDUCAO_BCALC').Asfloat    := vISUB_REDUCAO_BCALC;
  tq1.parambyname('P_ISUB_PERCENT_SUBSTIT').Asfloat  := vISUB_PERCENT_SUBSTIT;
  tq1.parambyname('P_SOMASTNF').AsString             := vSOMASTNF;
  tq1.parambyname('P_MODBCST').AsString              := vMODBCST;

  tq1.execsql;
  tq1.close;
  tq1.Free;
  result := 1;
  tq.close;
  tq.Free;
end;

function TCLSSubstTrib.Gravar(pUFSIGLA: String): Byte;
var
  tq, tq1: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add('SELECT * FROM MERCADORIA_NCM where MNCM_ID_NCM=:P_MNCM_CODIGO');
  tq.parambyname('P_MNCM_CODIGO').AsString := pUFSIGLA;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO MERCADORIA_NCM  (DESCRICAO,MNCM_NF_CLASSFISCAL,MNCM_IMPOSTO_IPI,MNCM_IMPOSTO_PIS,MNCM_IMPOSTO_COFINS,MNCM_IMPOSTO_ISSQN,MNCM_CST_IPI,MNCM_CST_PIS,MNCM_CST_COFINS,MNCM_ID_NCM,MNCM_DESCRICAO_TIPI,MNCM_CODIGO, SOMASTNF) '+
               ' VALUES(:P_MNCM_DESCRICAO,:P_MNCM_NF_CLASSFISCAL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,:pUFSIGLA,:P_MNCM_DESCRICAO_TIPI,:P_MNCM_CODIGO, :P_SOMASTNF) ');
  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE MERCADORIA_NCM SET DESCRICAO = :P_MNCM_DESCRICAO,MNCM_NF_CLASSFISCAL = :P_MNCM_NF_CLASSFISCAL, MNCM_DESCRICAO_TIPI = :P_MNCM_DESCRICAO_TIPI, MNCM_CODIGO = :P_MNCM_CODIGO WHERE MNCM_ID_NCM = :pUFSIGLA, SOMASTNF = :P_SOMASTNF ');
  end;
  tq1.parambyname('pUFSIGLA').AsString        := pUFSIGLA;
  tq1.execsql;
  tq1.close;
  tq1.Free;
  result := 1;
  tq.close;
  tq.Free;
end;

function TCLSSubstTrib.RetornaMaiorCodigo(): Integer;
var
  tq1: TQuery;
begin
  tq1 := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active := False;
  tq1.close;
  tq1.sql.Clear;
  tq1.params.clear;
  tq1.sql.add('SELECT MAX(MNCM_ID_NCM) AS MAIORCODIGO FROM MERCADORIA_NCM ');
  tq1.Open;
  if trim(tq1.FieldByName('MAIORCODIGO').AsString) = '' then
    Result := 0
  else
    Result := tq1.FieldByName('MAIORCODIGO').AsInteger;
  tq1.close;
  tq1.Free;
end;

end.
